Automation Studio new Data Views to improve Automation Health
It has been a while since Salesforce introduced new Data Views. Well, this is no longer the case as with the recent release of Marketing Cloud (05/10/2022) comes not only one but two new Data Views. _AutomationInstance and _AutomationActivityInstance will help you identify points of failure and improve the efficiency and success rate of your automations by reviewing their health.
Data View: Automation Instance
Wondering which automations failed yesterday? Or which ones skipped this week? Or maybe you need an average duration of each automation in a specific Business Unit?
These are some examples of what you can accomplish by querying this Data View. It helps to get an overview of all automations within a tenant and improve the efficiency and success rate by continuously reviewing their health.
What _AutomationInstance Data View Shows
- Automations that ran recently
- When an automation ran
- The duration of an automation
- Whether an automation succeeded, skipped, or failed
- The reason an automation failed
- The file that was being processed when a triggered automation failed
This Data View is available by default on all accounts. The data for the last 31 days is available 1 day after the automation runs.
Similar to _Job, this Data View holds specific Child BU’s data. You can not query `_AutomationInstance` from the Parent BU
Dates are in UTC
_AutomationInstance Columns
| NAME | DESCRIPTION | DATA EXTENSION DATA TYPE |
|---|---|---|
| MemberID | The unique ID of the business unit. | Number |
| AutomationName | The automation name. | Text |
| AutomationDescription | The automation description. | Text |
| AutomationCustomerKey | The unique ID of the automation. | Text |
| AutomationType | The automation’s starting source. Possible values are Schedule, File Drop, or Trigger. | Text |
| AutomationNotificationRecipient_Complete | The email address where notifications about completed automations are sent. | Text |
| AutomationNotificationRecipient_Error | The email address where notifications about automation errors are sent. | Text |
| AutomationNotificationRecipient_Skip | The email address where notifications about skipped automations are sent. | Text |
| AutomationStepCount | The number of steps in the automation. | Number |
| AutomationInstanceID | The unique ID of the automation run. | Text |
| AutomationInstanceIsRunOnce | Whether the automation was set to run once. 1 = true, 0 = false. | Boolean |
| FilenameFromTrigger | For file drop and trigger automations, the file that started the automation. | Text |
| AutomationInstanceScheduledTime_UTC | For scheduled automations, the time that the run was scheduled to begin. | Date |
| AutomationInstanceStartTime_UTC | The time that the automation run started. This value is blank if the run hasn’t started. | Date |
| AutomationInstanceEndTime_UTC | The time that the automation run ended. This value is blank if the run hasn’t ended. | Date |
| AutomationInstanceStatus | The status of the automation. Possible values are QueuedFile, Initialized, Executing, Stopped, Complete, or Error. | Text |
| AutomationInstanceActivityErrorDetails | The message from the error log, if applicable. If a system or unclassified error occurs, the value is System Error. If multiple errors occur, only the first message is displayed. To get details on multiple errors, use the _AutomationActivityInstance view. | Text |
Note that there is a typo in the official documentation. The name of the column is AutomationNotificationRecipient_Complete instead of AutomationNotificationRecipient_Compete.
Data View: Automation Activity Instance
Are you looking for activities at risk of timing out? Do you need the average duration of each activity inside an automation? Or maybe a list of activities that failed because of an error?
This Data View stores all runs of activities inside your automations. It can be used to prevent failures by identifying activities that often fail or run long.
What _AutomationActivityInstance Data View Shows
- Activities that ran in an automation
- When an activity ran
- The duration of an activity
- Whether an activity succeeded or failed
- The reason an activity failed
This Data View is available by default on all accounts. The data for the last 31 days is available 1 day after the automation runs.
Similar to _Job, this Data View holds specific Child BU’s data. You can not query `_AutomationActivityInstance` from the Parent BU
Dates are in UTC
Example: Which Automations Are Failing and Why?
An automation error can be categorized as a configuration error or system error.
- A configuration error occurs because an activity or dependency isn’t set up correctly. For example, if a file transfer activity is looking for a file that doesn’t exist.
- A system error is more general and often temporary—for example, a timeout error for a query trying to run for more than 30 minutes.
Before having access to this Data View, the quickest and easiest way to have more details about an automation’s error that is more detailed than the standard “an error occurred while….” was by asking the support. This is no longer the case as you can query _AutomationActivityInstance to find activities that failed because of a configuration error and check the error message to learn how you can address the error. Use this query from:
SELECT *
FROM [_automationactivityinstance]
WHERE activityinstancestatus = 'Error'
AND activityinstancestatusdetails LIKE 'System Error occurred%'
The same way, but this time you can use this query to get activities that failed because of a system error:
SELECT *
FROM [_automationactivityinstance]
WHERE activityinstancestatus = 'Error'
AND activityinstancestatusdetails NOT LIKE 'System Error occurred%'
_AutomationActivityInstance Columns
| NAME | DESCRIPTION | DATA EXTENSION DATA TYPE |
|---|---|---|
| MemberID | The unique ID of the business unit. | Number |
| AutomationName | The automation name. | Text |
| AutomationCustomerKey | The unique ID of the automation. | Text |
| AutomationInstanceID | The unique ID of the automation run. | Text |
| ActivityType | The activity type. Possible values are listed in the Activity Type IDs table below. | Number |
| ActivityName | The activity name. | Text |
| ActivityDescription | The activity description. | Text |
| ActivityCustomerKey | The unique ID of the activity. | Text |
| ActivityInstanceStep | Where the activity occurs in the automation. For example, 3.2 is step 3, activity 2. | Text |
| ActivityInstanceID | The unique ID of the activity run. | Text |
| ActivityInstanceStartTime_UTC | The time that the activity run started. | Date |
| ActivityInstanceEndTime_UTC | The time that the activity run ended. | Date |
| ActivityInstanceStatus | The status of the activity. Possible values are Initialized, Executing, Complete, Error, or NotSelected. | Text |
| ActivityInstanceErrorDetails | The message from the error log, if applicable. If a system or unclassified error occurs, the value is System Error. | Text |
And to get all the details you need here are the ActivityType possible IDs:
Activity Type IDs
| ACTIVITY TYPE ID | ACTIVITY NAME |
|---|---|
| 33 | SMS Activity |
| 42 | Send Email |
| 43 | Import File |
| 45 | Refresh Group |
| 53 | File Transfer |
| 73 | Data Extract |
| 84 | Report Definition |
| 300 | SQL Query |
| 303 | Filter |
| 423 | Script |
| 425 | Data Factory Utility Activity |
| 426 | Refresh Segment Template |
| 427 | Publish Audience |
| 467 | Wait |
| 724 | Refresh Mobile Filtered List |
| 725 | Send SMS |
| 726 | Import Mobile Contacts |
| 733 | Journey Builder Event Activity |
| 736 | Send Push |
| 749 | Fire Event |
| 771 | Salesforce Email Send |
| 772 | Mobile Connect Send Salesforce Sync Subscriber |
| 783 | Send GroupConnect |
| 1000 | Verification |
| 1010 | Interaction Studio Data |
| 1101 | Interactions |
| 1701 | Batch Personalization |
| 3700 | Contact to Business Unit Mapping |
I have not yet played with these two Data Views, but very excited to check them out. I can already think of some interesting use cases where they can be used. I’ll share with you my findings, I promise ! In the meantime, have fun using SFMC !